package com.cms.biz.queryAnalyse.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.builder.meta.bizmodel.SYS_MDL_CTN;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.form.server.provider.plugins.util.FormDataUtil;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.metadata.bizmodel.SYS_MODEL;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;
import com.pub.util.StringFunction;


/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPTZDFormQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";
	
	protected     String                  msTableName1 = "";
	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		EFRowSet     queryRowset = EFRowSet.getInstance();
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			
			// 获取查询数据
			queryDataset = queryDataObject(stmt, PO);
			queryRowset.setExtProperty(EMPQueryParamEnum.PAGEQUERY, PO.getValue(EMPQueryParamEnum.PAGEQUERY, null));
			queryRowset.setDataSet(EMPQueryParamEnum.QUERYRESULT, queryDataset);
			// 封装返回数据
		   	RO = new JResponseObject(queryRowset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 组织数据的过程
	 * @throws Exception
	 * @return TreeTableDataManager
	 */
	public EFDataSet queryDataObject(JStatement stmt, JParamObject PO) throws Exception {
		EFDataSet     queryDataset = null;
		EFDataSet       newDataset = EFDataSet.getInstance();
		EMPPageQuery     pageQuery = (EMPPageQuery) PO.getValue(EMPQueryParamEnum.PAGEQUERY, "");

		queryDataset = getFormDataSet(stmt, PO, SYS_MDL_CTN._BIZ_CTN_TYPE_JBDS_);

		if(pageQuery == null) return queryDataset;
		
		pageQuery.setTotalCount(queryDataset.getRowCount());
		newDataset = EMPLimitConvertUtil.getDataSetLimit(queryDataset, pageQuery.getPageNum(), pageQuery.getNumPerPage());
		return newDataset;
	}
	
	public EFDataSet getFormDataSet(JStatement stmt, JParamObject PO, String BIZ_CTN_TYPE) {
		ResultSet          rs = null;
		String         strSql = " select ";
		String    strLeftJoin = "";
		String        CTN_FCT = PO.GetValueByParamName("CTN_FCT1", "");
		String      BILL_LEFT = PO.GetValueByParamName("BILL_LEFT", "").replaceAll("####", CTN_FCT);
		String     BILL_WHERE = PO.GetValueByParamName("BILL_WHERE", "").replaceAll("####", CTN_FCT);
		String     BILL_ORDER = PO.GetValueByParamName("BILL_ORDER", "").replaceAll("####", CTN_FCT);
		String    EXT_COLUMNS = PO.GetValueByParamName("EXT_COLUMNS", "");
		EFDataSet dictDataSet = EFDataSet.getInstance(CTN_FCT);
		EFDataSet   objColsDS = getSYS_OBJCOLS(stmt, CTN_FCT);
		EFRowSet     objColRS = null;
		EFRowSet      fkeyObj = null;

		try {			
			for(int i = 0; i < objColsDS.getRowCount(); i++) {
				objColRS = objColsDS.getRowSet(i);
				if(objColRS.getString("COL_ISFKEY", "1").equals("1")) {
					fkeyObj = getDictRow(stmt, objColRS.getString("COL_FOBJ", ""));
					if(fkeyObj == null) continue;
					strLeftJoin += " LEFT JOIN " + objColRS.getString("COL_FOBJ", "") + " " + objColRS.getString("COL_ID", "") + " ON " + objColRS.getString("COL_ID", "") 
					             + "." + fkeyObj.getString("DCT_BMCOLID", "")  + " = " + CTN_FCT + "." + objColRS.getString("COL_ID", "");
					strSql += CTN_FCT + "." + objColRS.getString("COL_ID", "") + " as " + objColRS.getString("COL_ID", "") + ","
					        + objColRS.getString("COL_ID", "") + "." + fkeyObj.getString("DCT_MCCOLID", "") 
					        + " as " + objColRS.getString("COL_ID", "") + "MC,";
				} else {
					strSql += CTN_FCT + "." + objColRS.getString("COL_ID", "") + " as " + objColRS.getString("COL_ID", "") + ",";
				}
			}
			
			if(!BILL_LEFT.equals("")) {
				strSql += EXT_COLUMNS;
			}
			
			strSql = strSql.substring(0, strSql.lastIndexOf(","));

			strSql += " from " + CTN_FCT + " " + strLeftJoin;
			
			if(!BILL_LEFT.equals("")) {
				strSql += BILL_LEFT;
			}
			
			strSql += " where " + BILL_WHERE.replaceAll("####", CTN_FCT);
			
			if(!BILL_ORDER.equals("")) {
				strSql += " order by " + BILL_ORDER;
			}
			rs = stmt.executeQuery(strSql);
			dictDataSet = DataSetUtils.resultSet2DataSet(rs, dictDataSet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, null, null);
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dictDataSet;
	}
	
	/**
	 * 获取字典列元数据
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public EFDataSet getSYS_OBJCOLS(JStatement stmt, String tableName) {
		ResultSet        rs = null;
		EFDataSet    dictDS = EFDataSet.getInstance("SYS_OBJCOLS");
		String       strSql = " select * from SYS_OBJCOLS where OBJ_ID = '" + tableName + "' and F_STAU = '1'";
		
		try {
			rs = stmt.executeQuery(strSql);
			dictDS = DataSetUtils.resultSet2DataSet(rs, dictDS);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dictDS;
	}
	
	/**
	 * 获取模型元数据
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public EFDataSet getSYS_MDL_VAL(JStatement stmt, String MDL_ID) {
		ResultSet        rs = null;
		EFDataSet  mdlValDS = EFDataSet.getInstance("SYS_MDL_VAL");
		String       strSql = " select * from SYS_MDL_VAL where MDL_ID = '" + MDL_ID + "'";

		try {
			rs = stmt.executeQuery(strSql);
			mdlValDS.setPrimeKey(new String[]{"MDL_KEY"});
			mdlValDS = DataSetUtils.resultSet2DataSet(rs, mdlValDS);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mdlValDS;
	}
	
	/**
	 * 获取字典元数据
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public EFRowSet getDictRow(JStatement stmt, String tableName) {
		ResultSet        rs = null;
		EFDataSet    dictDS = EFDataSet.getInstance("SYS_DICTS");
		EFRowSet     dictRS = null;
		String       strSql = " select * from SYS_DICTS where DCT_ID = '" + tableName + "'";
		
		try {
			rs = stmt.executeQuery(strSql);
			dictDS = DataSetUtils.resultSet2DataSet(rs, dictDS);
			if(dictDS != null && dictDS.getRowCount() > 0) {
				dictRS = dictDS.getRowSet(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dictRS;
	}
}
